/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.*;
import java.sql.*;
import javax.servlet.*;


import javax.servlet.http.*;

/**
 *
 * @author User
 */
public class ShowMemberServlet extends HttpServlet {

    private ServletConfig config;
    //Setting JSP page
    String page = "show-member.jsp";

    public void init(ServletConfig config)
            throws ServletException {

        this.config = config;

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
//        processRequest(request, response);
//        PrintWriter out = response.getWriter();
//
//        //Establish connection to MySQL database
//        String connectionURL = "jdbc:mysql://localhost:3306/cemilansehat";
//        Connection connection = null;
//        ResultSet rs;
//        response.setContentType("text/html");
//        List dataList = new ArrayList();
//        try {
//            // Load the database driver
//            Class.forName("com.mysql.jdbc.Driver");
//            // Get a Connection to the database
//            connection = DriverManager.getConnection(connectionURL, "root", "");
//            //Select the data from the database
//            String sql = "select * from member";
//            Statement s = connection.createStatement();
//            s.executeQuery(sql);
//            rs = s.getResultSet();
//            while (rs.next()) {
//                dataList.add(rs.getString(1));
//                dataList.add(rs.getString(2));
//                dataList.add(rs.getString(3));
//                dataList.add(rs.getString(4));
//                dataList.add(rs.getString(6));
//            }
//            rs.close();
//            s.close();
//        } catch (Exception e) {
//            System.out.println("Exception is ;" + e);
//        }
//        request.setAttribute("data", dataList);
//        //Disptching request
//        RequestDispatcher dispatcher = request.getRequestDispatcher(page);
//        if (dispatcher != null) {
//            dispatcher.forward(request, response);
//        }
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        PrintWriter out = response.getWriter();

        //Establish connection to MySQL database
        String connectionURL = "jdbc:mysql://localhost:3306/cemilansehat";
        Connection connection = null;
        ResultSet rs;
        response.setContentType("text/html");
        List dataList = new ArrayList();
        String member = request.getParameter("member");
        String kat_member = request.getParameter("kat_member");

        try {

            // Load the database driver
            Class.forName("com.mysql.jdbc.Driver");
            // Get a Connection to the database
            connection = DriverManager.getConnection(connectionURL, "root", "");
            //Select the data from the database
            if (member == null) {
                String sql = "select * from member";
                Statement s = connection.createStatement();
                s.executeQuery(sql);
                rs = s.getResultSet();
                while (rs.next()) {
                    dataList.add(rs.getString(1));
                    dataList.add(rs.getString(2));
                    dataList.add(rs.getString(3));
                    dataList.add(rs.getString(4));
                    dataList.add(rs.getString(6));
                }
                rs.close();
                s.close();
            } else {
                if(kat_member.equals("nama")){
                    String sql = "select * from member where nama_member like '%"+member+
                            "' or nama_member like '"+member+"%' or nama_member like '%"+member+"%'";
                Statement s = connection.createStatement();
                s.executeQuery(sql);
                rs = s.getResultSet();
                while (rs.next()) {
                    dataList.add(rs.getString(1));
                    dataList.add(rs.getString(2));
                    dataList.add(rs.getString(3));
                    dataList.add(rs.getString(4));
                    dataList.add(rs.getString(6));
                }
                rs.close();
                s.close();
                } else if(kat_member.equals("role")){
                    String sql = "select * from member where role like '%"+member+
                            "' or role like '"+member+"%' or role like '%"+member+"%'";
                Statement s = connection.createStatement();
                s.executeQuery(sql);
                rs = s.getResultSet();
                while (rs.next()) {
                    dataList.add(rs.getString(1));
                    dataList.add(rs.getString(2));
                    dataList.add(rs.getString(3));
                    dataList.add(rs.getString(4));
                    dataList.add(rs.getString(6));
                }
                rs.close();
                s.close();
                }else if(kat_member.equals("id member")){
                    String sql = "select * from member where id_member like '%"+member+
                            "' or id_member like '"+member+"%' or id_member like '%"+member+"%'";
                Statement s = connection.createStatement();
                s.executeQuery(sql);
                rs = s.getResultSet();
                while (rs.next()) {
                    dataList.add(rs.getString(1));
                    dataList.add(rs.getString(2));
                    dataList.add(rs.getString(3));
                    dataList.add(rs.getString(4));
                    dataList.add(rs.getString(6));
                }
                rs.close();
                s.close();
                } else if(kat_member.equals("jenis kelamin")){
                    String sql = "select * from member where jenis_kelamin like '%"+member+
                            "' or jenis_kelamin like '"+member+"%' or jenis_kelamin like '%"+member+"%'";
                Statement s = connection.createStatement();
                s.executeQuery(sql);
                rs = s.getResultSet();
                while (rs.next()) {
                    dataList.add(rs.getString(1));
                    dataList.add(rs.getString(2));
                    dataList.add(rs.getString(3));
                    dataList.add(rs.getString(4));
                    dataList.add(rs.getString(6));
                }
                rs.close();
                s.close();
                }else if(kat_member.equals("id member")){
                    String sql = "select * from member where email like '%"+member+
                            "' or email like '"+member+"%' or email like '%"+member+"%'";
                Statement s = connection.createStatement();
                s.executeQuery(sql);
                rs = s.getResultSet();
                while (rs.next()) {
                    dataList.add(rs.getString(1));
                    dataList.add(rs.getString(2));
                    dataList.add(rs.getString(3));
                    dataList.add(rs.getString(4));
                    dataList.add(rs.getString(6));
                }
                rs.close();
                s.close();
                }
                
            }
        } catch (Exception e) {
            System.out.println("Exception is ;" + e);
        }
        request.setAttribute("data", dataList);
        //Disptching request
        RequestDispatcher dispatcher = request.getRequestDispatcher(page);
        if (dispatcher != null) {
            dispatcher.forward(request, response);
        }
    }
}
